package com.andrew.dao.impl;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.andrew.dao.CommentDao;
import com.andrew.entity.Book;
import com.andrew.entity.Comment;
import com.andrew.entity.Customer;
import com.andrew.util.JDBCUtils;
import com.sun.org.apache.bcel.internal.generic.NEW;

public class CommentDaoImpl implements CommentDao {
	
private QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
	/**
	 * 添加评论
	 */
	@Override
	public int InsertComment(Comment comment) throws SQLException {
		String sql="INSERT INTO Comment VALUES(?,?,?,?,?)";
		return qr.update(sql,comment.getCommentId(),comment.getBook().getBookId(),comment.getCustomer().getCustomerId(),comment.getCommentContent(),comment.getCommentTime());
	}
	
	/**
	 * 查询某本书的所有评论
	 */
	@Override
	public List<Comment> queryAllCommentByBookId(int bookId) throws SQLException {
		List<Comment> comments=qr.query("select * from Comment where BookId=?",new BeanListHandler<Comment>(Comment.class),bookId);
		for(Comment comment:comments) {
			loadBook(comment);
			loadCustomer(comment);
		}
		
		return comments;
	}
	/**
	 * 根据评论用户信息
	 * @param comment
	 * @throws SQLException
	 */
	private void loadCustomer(Comment comment) throws SQLException {
		Customer customer=qr.query("select * from Customer where CustomerId=(SELECT CustomerId FROM COMMENT WHERE CommentId=?)", new BeanHandler<Customer>(Customer.class),comment.getCommentId());
		comment.setCustomer(customer);
	}
	
	/**
	 * 根据评论加载书本信息
	 * @param comment
	 * @throws SQLException
	 */
	private void loadBook(Comment comment) throws SQLException {
		Book book=qr.query("SELECT * FROM book WHERE BookId=(SELECT BookId FROM COMMENT WHERE CommentId=?)",new BeanHandler<Book>(Book.class),comment.getCommentId());
		comment.setBook(book);
	}
	
	/**
		删除评论
	 */
	@Override
	public int deleteCommentByCommentId(int CommentId) throws SQLException {
		String sql="Delete from Comment where CommentId=?";
		return qr.update(sql,CommentId);
	}

	@Override
	public List<Comment> queryAllComments() throws SQLException {
		List<Comment> comments=qr.query("select * from Comment",new BeanListHandler<Comment>(Comment.class));
		for(Comment comment:comments) {
			loadBook(comment);
			loadCustomer(comment);
		}
		return comments;
		
	}

}
